package com.chenjinyan.back.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.chenjinyan.back.dao.UserRepository;
import com.chenjinyan.back.entity.RegionEntity;
import com.chenjinyan.back.entity.UserEntity;
import com.chenjinyan.back.service.UserService;
import com.chenjinyan.back.utils.ResultUtil;
import com.chenjinyan.back.utils.StringUtils;
import com.chenjinyan.back.vo.Result;
import com.chenjinyan.back.vo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 */
@Service
@Transactional
public class UserServiceImpl implements UserService {

    @Autowired
    UserRepository userRepository;

    @PersistenceContext
    private EntityManager em;


    @Override
    public Result findUser(User user) {
        Result result = ResultUtil.success();
        Long id = user.getId();
        String loginAccount = user.getLoginAccount();
        Date createTime = user.getCreateTime();
        Date loginTime = user.getLoginTime();
        String pageNo = user.getPageNo();
        String pageSize = user.getPageSize();
        String createTimeS = null;
        String loginTimeS = null;
        Map<String, String> map = new HashMap<>(16);


        if (null == pageNo || "".equals(pageNo)) {
            pageNo = "1";
        }
        if (null == pageSize || "".equals(pageSize)) {
            pageSize = "20";
        }
        if (null != createTime) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            createTimeS = sdf.format(createTime);
        }
        if (null != loginTime) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            loginTimeS = sdf.format(loginTime);
        }

        StringBuilder sql = builderSql();
        StringBuilder sqlCount = builderSqlCount();

        if (null != id) {
            //用户id
            sql.append(" AND ID =:id ");
            sqlCount.append(" AND ID =:id ");
        }

        if (null != loginAccount && !"".equals(loginAccount)) {
            //用户id
            sql.append(" AND LOGIN_ACCOUNT like CONCAT('%',:loginAccount,'%') ");
            sqlCount.append(" AND LOGIN_ACCOUNT like CONCAT('%',:loginAccount,'%') ");
        }
        if (null != createTimeS) {
            //失物种类
            sql.append(" AND date_format(CREATE_TIME,'%Y-%m-%d') = date_format(:createTimeL,'%Y-%m-%d') ");
            sqlCount.append(" AND date_format(CREATE_TIME,'%Y-%m-%d') = date_format(:createTimeL,'%Y-%m-%d') ");
        }
        if (null != loginTimeS) {
            //开始时间
            sql.append(" AND date_format(LOGIN_TIME,'%Y-%m-%d') = date_format(:loginTimeL,'%Y-%m-%d') ");
            sqlCount.append(" AND date_format(LOGIN_TIME,'%Y-%m-%d') = date_format(:loginTimeL,'%Y-%m-%d') ");
        }

        Query q = em.createNativeQuery(sql.toString(), UserEntity.class);
        Query countQ = em.createNativeQuery(sqlCount.toString(), UserEntity.class);

        if (null!=id) {
            q.setParameter("id", id);
            countQ.setParameter("id", id);
        }

        if (!StringUtils.isEmpty(loginAccount) && !StringUtils.isEmpty(loginAccount)) {
            q.setParameter("loginAccount", loginAccount);
            countQ.setParameter("loginAccount", loginAccount);
        }

        if (null != createTimeS) {
            q.setParameter("createTimeL", createTimeS);
            countQ.setParameter("createTimeL", createTimeS);
        }

        if (null != loginTimeS) {
            q.setParameter("loginTimeL", loginTimeS);
            countQ.setParameter("loginTimeL", loginTimeS);
        }

        q.setFirstResult((Integer.parseInt(pageNo) - 1) * Integer.parseInt(pageSize));
        q.setMaxResults(Integer.parseInt(pageSize));
        List<UserEntity> list = q.getResultList();
        if (null != list && list.size() > 0) {
            for (UserEntity userEntity : list) {
                StringBuilder sqlRegion = builderRegionSql();
                Query query = em.createNativeQuery(sqlRegion.toString(), RegionEntity.class);
                query.setParameter("provinceId", userEntity.getProvince());
                query.setParameter("cityId", userEntity.getCity());
                query.setParameter("districtId", userEntity.getDistrict());
                List<RegionEntity> regionEntities = query.getResultList();
                userEntity.setProvinceName(regionEntities.get(0).getProvinceName());
                userEntity.setCityName(regionEntities.get(0).getCityName());
                userEntity.setDistrictName(regionEntities.get(0).getDistrictName());
            }
        }

        Integer recordCount = countQ.getResultList().size();
        //Integer recordCount = q.getResultList().size();
        Long pageCount = getPageCount(Integer.parseInt(pageSize), recordCount.longValue());

        map.put("currentPage", pageNo);
        map.put("pageSize", pageSize);
        map.put("recordCount", String.valueOf(recordCount));
        if(pageCount==0){
            pageCount++;
        }
        map.put("pageCount", String.valueOf(pageCount));
        map.put("data", JSONObject.toJSONString(list, SerializerFeature.WriteMapNullValue));
        result.setData(map);
        return result;
    }

    @Override
    public Result delUser(List<String> ids) throws ParseException {
        Result result = ResultUtil.success();
        StringBuilder getSql = builderGetSql();
        Query query = em.createNativeQuery(getSql.toString(), UserEntity.class);
        query.setParameter("ids", ids);

        List<UserEntity> list = query.getResultList();
        if (null != list && list.size() == ids.size()) {
            StringBuilder sql = builderDelSql();
            Query q = em.createNativeQuery(sql.toString());
            q.setParameter("ids", ids);
            q.executeUpdate();
            result.setMsg("删除成功");
        } else {
            result.setCode("000001");
            result.setMsg("fail");
            result.setMsg("删除失败，请检查传入id");
        }
        return result;
    }

    private Long getPageCount(Integer pageSize, Long recordCount) {
        return recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1;
    }

    private StringBuilder builderSql() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("ID,LOGIN_ACCOUNT,PASSWORD,NET_NAME,PHONE,BIRTHDAY,EMAIL,AREA,NAME,CREATE_TIME,LOGIN_TIME,PROVINCE,CITY,DISTRICT ")
                .append("FROM lost_and_find_user ")
                .append("WHERE 1=1 ");
        return sql;
    }

    private StringBuilder builderSqlCount() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT ")
                .append("ID,LOGIN_ACCOUNT,PASSWORD,NET_NAME,PHONE,BIRTHDAY,EMAIL,AREA,NAME,CREATE_TIME,LOGIN_TIME,PROVINCE,CITY,DISTRICT ")
                .append("FROM lost_and_find_user ")
                .append("WHERE 1=1 ");
        return sql;
    }

    private StringBuilder builderDelSql() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("DELETE ")
                .append("FROM lost_and_find_user ")
                .append("WHERE ID IN :ids ");
        return sql;
    }

    private StringBuilder builderGetSql() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT * FROM ")
                .append("lost_and_find_user ")
                .append("WHERE ID IN :ids");
        return sql;
    }

    private StringBuilder builderRegionSql() {
        StringBuilder sql = new StringBuilder(500);
        sql.append("SELECT * FROM ")
                .append("lost_and_find_region ")
                .append("WHERE PROVINCE_ID= :provinceId and CITY_ID= :cityId and DISTRICT_ID= :districtId");
        return sql;
    }
}
